USE [EX2_Employees_Dev]
GO

/****** Object:  StoredProcedure [dbo].[StartNewAssessment]    Script Date: 08/06/2012 13:39:57 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--Stored procedure to create a new assessment record and all its dependencies which
--are skill assessments and goals

ALTER PROCEDURE StartNewAssessment 
	@periodId INT,
	@employeeId INT,
	@managerId INT,
	@statusId INT,
	@goalsAmount INT
AS
BEGIN
	DECLARE @teamId INT
	DECLARE @assessmentId INT
	DECLARE @hiredDate DATETIME
	DECLARE @fromDate DATETIME
	DECLARE @toDate DATETIME
	
	SET NOCOUNT ON
	SELECT @teamId = e.[IdTeam], @hiredDate = e.[StartDate] FROM [dbo].[Employees] e
		WHERE e.[ID] = @employeeId
	
	SELECT @fromDate = p.[FromDate], @toDate = p.[ToDate] FROM [dbo].[AssessmentPeriods] p
		WHERE p.[ID] = @periodId
	
	--Gets the correct start date for the specific user	
	IF @hiredDate > @fromDate
	BEGIN
		SET @fromDate = @hiredDate
	END
	
	--Creates the new assessment
	INSERT INTO [dbo].[Assessments]
           ([IdAssessmentPeriod]
           ,[IdEmployee]
           ,[FromDate]
           ,[ToDate]
           ,[IdAssessmentStatus]
           ,[IdManager])
     VALUES
           (@periodId
           ,@employeeId
           ,@fromDate
           ,@toDate
           ,@statusId
           ,@managerId);
	
	--Inserts the skill assessment for the record created previously
	SET @assessmentId = SCOPE_IDENTITY();
	INSERT INTO [dbo].[SkillAssessments] 
			([IdSkill]
			,[IdAssessment]
			,[DisplayOrder])
	SELECT s.[IdSkill], @assessmentId, s.[DisplayOrder] FROM [dbo].[SkillsPerTeam] s
	WHERE s.[IdTeam] = @teamId;
	
	--Inserts the specified number of goals for the assessment
	WHILE (@goalsAmount > 0)
	BEGIN
		INSERT INTO [dbo].[Goals]
			(IdAssessment
			,Achieved)
		VALUES
			(@assessmentId
			,0)
		SET @goalsAmount = @goalsAmount - 1
	END
	SET NOCOUNT OFF	
	SELECT @assessmentId
END
GO